﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace SisCRA.Database
{
    public class StudentDAL
    {
        #region -Constants-

        private const string TABLE = "ALUNO";
        private const string INSERT_USER = "INSERT INTO T_ALUNO (DC_PRONTUARIO, DC_NOME, DC_EMAIL) VALUES (@PRONTUARIO, @NOME, @EMAIL)";
        private const string UPDATE_USER = "UPDATE T_ALUNO SET DC_PRONTUARIO = @PRONTUARIO, DC_NOME = @NOME, DC_EMAIL = @EMAIL WHERE PK_ALUNO_ID = @ID";
        private const string GET_STUDENT_BY_ID = "SELECT PK_ALUNO_ID AS ID, DC_PRONTUARIO AS PRONTUARIO, DC_NOME AS NOME, DC_EMAIL AS EMAIL FROM T_ALUNO WHERE PK_ALUNO_ID = @ID";
        private const string GET_STUDENT_BY_CODE = "SELECT PK_ALUNO_ID AS ID, DC_PRONTUARIO AS PRONTUARIO, DC_NOME AS NOME, DC_EMAIL AS EMAIL FROM T_ALUNO WHERE DC_PRONTUARIO = @PRONTUARIO";
        private const string GET_STUDENT_BY_NAME = "SELECT PK_ALUNO_ID AS ID, DC_PRONTUARIO AS PRONTUARIO, DC_NOME AS NOME, DC_EMAIL AS EMAIL FROM T_ALUNO WHERE DC_NOME LIKE @NOME";
        private const string GET_ALL = "SELECT PK_ALUNO_ID AS ID, DC_PRONTUARIO AS PRONTUARIO, DC_NOME AS NOME, DC_EMAIL AS EMAIL FROM T_ALUNO";

        #endregion

        #region -Private Attributes-

        MySqlConnection conn;
        MySqlCommand cmd;
        MySqlDataAdapter da;

        #endregion

        #region -Public Methods-

        /// <summary>
        /// Método que insere um aluno no banco de dados.
        /// </summary>
        /// <param name="code">Prontuário do aluno</param>
        /// <param name="name">Nome do aluno</param>
        /// <param name="email">E-mail do aluno</param>
        public void InsertStudent(string code, string name, string email)
        {
            try
            {
                conn = Connection.Instance.Conn;
                conn.Open();
                cmd = new MySqlCommand(INSERT_USER, conn);
                cmd.Parameters.AddWithValue("@PRONTUARIO", code);
                cmd.Parameters.AddWithValue("@NOME", name);
                cmd.Parameters.AddWithValue("@EMAIL", email);
                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// Método que atualiza um aluno no banco de dados.
        /// </summary>
        /// <param name="id">Id do aluno</param>
        /// <param name="code">Prontuário do aluno</param>
        /// <param name="name">Nome do aluno</param>
        /// <param name="email">E-mail do aluno</param>
        public void UpdateStudent(int id, string code, string name, string email)
        {
            try
            {
                conn = Connection.Instance.Conn;
                conn.Open();
                cmd = new MySqlCommand(UPDATE_USER, conn);
                cmd.Parameters.AddWithValue("@PRONTUARIO", code);
                cmd.Parameters.AddWithValue("@NOME", name);
                cmd.Parameters.AddWithValue("@EMAIL", email);
                cmd.Parameters.AddWithValue("@ID", id);
                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// Método que busca um aluno pelo Id.
        /// </summary>
        /// <param name="id">Id do aluno</param>
        /// <returns>DataTable contendo a busca realizada</returns>
        public DataTable GetStudentById(int id)
        {
            DataTable dt = new DataTable(TABLE);

            try
            {
                conn = Connection.Instance.Conn;
                conn.Open();
                cmd = new MySqlCommand(GET_STUDENT_BY_ID, conn);
                cmd.Parameters.AddWithValue("@ID", id);
                da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Dispose();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// Método que busca um aluno pelo Prontuário.
        /// </summary>
        /// <param name="code">Prontuário do aluno</param>
        /// <returns>DataTable contendo a busca realizada</returns>
        public DataTable GetStudentByCode(string code)
        {
            DataTable dt = new DataTable(TABLE);

            try
            {
                conn = Connection.Instance.Conn;
                conn.Open();
                cmd = new MySqlCommand(GET_STUDENT_BY_CODE, conn);
                cmd.Parameters.AddWithValue("@PRONTUARIO", code);
                da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Dispose();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// Método que busca aluno(s) pelo Nome.
        /// </summary>
        /// <param name="name">Nome do aluno</param>
        /// <returns>DataTable contendo a busca realizada</returns>
        public DataTable GetStudentByName(string name)
        {
            DataTable dt = new DataTable(TABLE);

            try
            {
                conn = Connection.Instance.Conn;
                conn.Open();
                cmd = new MySqlCommand(GET_STUDENT_BY_NAME, conn);
                cmd.Parameters.AddWithValue("@NOME", "%" + name + "%");
                da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Dispose();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// Método que busca todos os alunos.
        /// </summary>
        /// <returns>DataTable contendo busca realizada</returns>
        public DataTable GetAll()
        {
            DataTable dt = new DataTable(TABLE);

            try
            {
                conn = Connection.Instance.Conn;
                conn.Open();
                cmd = new MySqlCommand(GET_ALL, conn);
                da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Dispose();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        #endregion
    }
}
